CREATE Proc SPJSAccount
	@UserID int ,
	@Email varchar(50),
	@Password  varchar(10)  ,
	@FirstName  nvarchar(30),
	@LastName  nvarchar(30),
	@Newsletter bit,
	@JobAlert bit,
	@CompanyInfo bit
AS
declare @ReturnValue int
set @ReturnValue=0
if(@UserID=0)
	begin
		insert into tblJSAccounts
		(Email,Password,
		FirstName,LastName,
		Newsletter,JobAlert,
		CompanyInfo)
		values(@Email,
		@Password,@FirstName,
		@LastName,@Newsletter,
		@JobAlert,@CompanyInfo)
		set @ReturnValue=@@IDENTITY
	end
else
	begin
		update tblJSAccounts
		set  FirstName=@FirstName,
		LastName=@LastName,
		Newsletter=@Newsletter,
		JobAlert=@JobAlert,
		CompanyInfo=@CompanyInfo
		where UserID= @UserID 
	end
return @ReturnValue
GO




Create proc SPJSPersonal
	@UserID int,
	@Address nvarchar(50),
	@Resident nvarchar(50),
	@ProvinceID tinyint,
	@City nvarchar(20),
	@DOB smalldatetime,
	@Gender bit,
	@MaritalStatus tinyint,
	@RaceID tinyint
AS
	update tblJSAccounts
	set UserID=@UserID ,
	Address= @Address,
	Resident= @Resident,
	ProvinceID= @ProvinceID,
	City= @City,DOB= @DOB,
	Gender= @Gender,
	MaritalStatus= @MaritalStatus,
	RaceID=@RaceID
	where UserID=@UserID

GO

create proc SPJSEducation
	@EducationID int,
	@UserID int,
	@School nvarchar(50),
	@Country varchar(15),
	@Faculty nvarchar(50),
	@Major nvarchar(50),
	@Mode nvarchar(20),
	@FromMonthYear varchar(7),
	@ToMonthYear varchar(7),
	@Description nvarchar(100)
as
	if(@EducationID=0)
		insert into tblJSEducations
		(UserID, School,
		Country,	Faculty,
		Major,Mode,
		FromMonthYear,
		ToMonthYear,
		Description) values(
		@UserID, @School,
		@Country,@Faculty,
		@Major,@Mode,
		@FromMonthYear,
		@ToMonthYear,
		@Description
		)		 
	else
		update tblJSEducations
		set School=@School,
		Country=@Country,
		Faculty=@Faculty,
		Major=@Major,
		Mode=@Mode,
		FromMonthYear=@FromMonthYear,
		ToMonthYear=@ToMonthYear,
		Description=@Description
		where EducationID=@EducationID

GO

CREATE proc SPJSExperience
	@ExperienceID int,
	@UserID int,
	@Company nvarchar(50),
	@Address nvarchar(50),
	@Country varchar(15),
	@JobCategoryID tinyint,
	@JobPosition nvarchar(50),
	@JobSummary nvarchar(100),
	@Salary int,
	@FromMonthYear varchar(7),
	@ToMonthYear varchar(7),
	@Description nvarchar(100)
as
	if(@ExperienceID=0)
		insert into tblJSExperiences
		(UserID, Company,Address,
		Country,JobCategoryID,
		JobPosition,JobSummary,Salary,
		FromMonthYear,
		ToMonthYear,
		Description) values(
		@UserID, @Company,@Address,
		@Country,@JobCategoryID,
		@JobPosition,@JobSummary,@Salary,
		@FromMonthYear,
		@ToMonthYear,
		@Description
		)		 
	else
		update tblJSExperiences
		set Company=@Company,
		Address=@Address,
		Country=@Country,
		JobCategoryID=@JobCategoryID,
		JobPosition=@JobPosition,
		JobSummary=@JobSummary,
		FromMonthYear=@FromMonthYear,
		ToMonthYear=@ToMonthYear,
		Salary=@Salary,
		Description=@Description
		where ExperienceID=@ExperienceID
GO

CREATE proc SPJSSkill
	@SkillID int,
	@UserID int,
	@Speed nvarchar(50),
	@Software nvarchar(100),
	@VNSpeaking varchar(20),
	@VNReading varchar(20),
	@VNListening varchar(20),
	@ENSpeaking varchar(20),
	@ENReading varchar(20),
	@ENListening varchar(20),
	@Description nvarchar(100)
as
	if(@SkillID=0)
		insert into tblJSSkills
		(UserID, Speed,
		Software,VNSpeaking,
		VNReading,VNListening,
		ENSpeaking,
		ENReading,ENListening,
		Description) values(
		@UserID, @Speed,
		@Software,@VNSpeaking,
		@VNReading,@VNListening,
		@ENSpeaking,
		@ENReading,@ENListening,
		@Description
		)		 
	else
		update tblJSSkills
		set Speed=@Speed,
		Software=@Software,VNSpeaking=@VNSpeaking,
		VNReading=@VNReading,VNListening=@VNListening,
		ENSpeaking=@ENSpeaking,
		ENReading=@ENReading,ENListening=@ENListening,		
		Description=@Description
		where SkillID=@SkillID
GO

CREATE proc SPJSResume
	@ResumeID int,
	@UserID int,
	@ResumeTitle nvarchar(50),
	@ContactEmail varchar(50),
	@Objective nvarchar(100),
	@ExperienceYear tinyint,
	@CurrentCompany nvarchar(50),
	@Viewable tinyint,
	@TemplateResume tinyint,
	@Confidental bit
as
	if(@ResumeID=0)
		insert into tblJSResumes
		(UserID, ResumeTitle,
		ContactEmail,Objective,
		ExperienceYear,CurrentCompany,
		Viewable,TemplateResume,
		Confidental) values(
		@UserID, @ResumeTitle,
		@ContactEmail,@Objective,
		@ExperienceYear,@CurrentCompany,
		@Viewable,@TemplateResume,
		@Confidental
		)		 
	else
		update tblJSResumes
		set ResumeTitle=@ResumeTitle,
		ContactEmail=@ContactEmail,
		Objective=@Objective,
		ExperienceYear=@ExperienceYear,
		CurrentCompany=@CurrentCompany,Viewable=@Viewable,
		TemplateResume=@TemplateResume,
		Confidental=@Confidental 
		where ResumeID=@ResumeID
GO


CREATE proc SPJSResumeDetail
	@Flag bit,
	@ResumeID int,
	@JobPosition nvarchar(50),
	@JobCategoryID tinyint,
	@JobIndustryID tinyint,
	@ProvinceID tinyint,
	@City nvarchar(30),
	@SalaryFrom int,
	@SalaryTo int,
	@Currency  char(3),
	@SalaryNegotive bit,
	@Relocate bit,
	@WorkType nvarchar(20),
	@ExperienceLevel nvarchar(20),
	@Available tinyint
as	
	if(@Flag=0)
	begin
		insert into tblJSResumeDetails
		(ResumeID,JobPosition, JobCategoryID,
		JobIndustryID,ProvinceID,City,
		SalaryFrom,SalaryTo,
		Currency,SalaryNegotive,
		Relocate,WorkType,
		ExperienceLevel,Available) values(
		@ResumeID,@JobPosition, @JobCategoryID,
		@JobIndustryID,@ProvinceID,@City,
		@SalaryFrom,@SalaryTo,
		@Currency,@SalaryNegotive,
		@Relocate,@WorkType,
		@ExperienceLevel,@Available
		)		 		
	end
	else
		update tblJSResumeDetails
		set JobPosition=@JobPosition,
		JobCategoryID=@JobCategoryID,
		JobIndustryID=@JobIndustryID,
		ProvinceID=@ProvinceID,City=@City,
		SalaryFrom=@SalaryFrom,
		SalaryTo=@SalaryTo,
		Relocate=@Relocate,		
		WorkType=@WorkType,
		ExperienceLevel=@ExperienceLevel,
		Available=@Available
		where ResumeID=@ResumeID
GO

create proc SPJSOthers
	@Flag bit,
	@ResumeID int,
	@Strength nvarchar(200),
	@Weekness nvarchar(200),
	@Hobbies nvarchar(200),
	@Family nvarchar(200),
	@Reference nvarchar(200)
as	
	if(@Flag=0)
	begin
		insert into tblJSOthers
		(ResumeID,Strength, Weekness,
		Hobbies,Family,Reference) values(
		@ResumeID,@Strength, @Reference,
		@Hobbies,@Family,@Reference
		)		 		
	end
	else
		update tblJSOthers
		set Strength=@Strength,
		Weekness=@Weekness,
		Hobbies=@Hobbies,
		Family=@Family,Reference=@Reference 
		where ResumeID=@ResumeID

GO
